package com.central.dataManage.common.datasourcePlugin;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLDataTypeImpl;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.expr.SQLNullExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlUnique;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlAlterTableModifyColumn;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcConstants;
import com.central.common.enums.DbType;
import com.central.common.model.ColumnInfo;
import com.central.common.model.IndexInfo;
import com.central.dataManage.common.utils.CommonUtils;
import com.central.dataManage.model.DatabaseInfo;
import com.central.common.model.DatasourceInfo;
import com.central.common.model.TableInfo;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;

import static com.central.dataManage.common.Constants.COM_MYSQL_JDBC_DRIVER;

/**
 * @author Tindy
 * @date 2021/7/5
 * @describe mysql数据源插件
 */
public class MysqlDatasourcePlugin extends BaseDatasourcePlugin {
    private String[] filterCatalogs = new String[]{"information_schema", "mysql", "sys", "performance_schema"};

    public MysqlDatasourcePlugin(DatasourceInfo datasource) {
        super(datasource);
    }

    @Override
    public String getJdbcUrl() {
        String address = "jdbc:mysql://" + getDatasource().getHost() + ":" + getDatasource().getPort() + "/";
        if (address.lastIndexOf("/") != (address.length() - 1)) {
            address += "/";
        }
        String jdbcUrl = address;
        if (!StringUtils.isBlank(getDatasource().getDbName())) {
            jdbcUrl = address + getDatasource().getDbName();
        }
        String other = buildOther(getDatasource().getOther(), "&");
        if (StringUtils.isNotEmpty(other)) {
            jdbcUrl += "?" + other;
        }
        return jdbcUrl;
    }


    @Override
    public Connection getConn() throws SQLException {
        Connection conn = null;
        try {
            String driver=StringUtils.isNotEmpty(getDatasource().getDriver())?getDatasource().getDriver():getDatasourceDriver();
            Class.forName(driver);
            conn = DriverManager.getConnection(getJdbcUrl(), getDatasource().getUsername(), CommonUtils.decodePassword(getDatasource().getPassword()));
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage(), e);
        }
        return conn;
    }

    @Override
    public List<DatabaseInfo> getDatabases(Connection conn) throws SQLException {
        List<String> filterCatalogList = Arrays.asList(filterCatalogs);
        ArrayList<DatabaseInfo> databases = new ArrayList<>();
        if(!StringUtils.isBlank(getDatasource().getDbName())){
            DatabaseInfo database = new DatabaseInfo();
            database.setDbName(getDatasource().getDbName());
            database.setDatasourceId(getDatasource().getId());
            database.setDatasourceType(DbType.MYSQL);
            databases.add(database);
        }else {
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW DATABASES");
            while (resultSet.next()) {
                String dbName = resultSet.getString(1);
                if (filterCatalogList.contains(dbName)) {
                    continue;
                }
                DatabaseInfo database = new DatabaseInfo();
                database.setDbName(dbName);
                database.setDatasourceId(getDatasource().getId());
                database.setDatasourceType(DbType.MYSQL);
                databases.add(database);
            }
            resultSet.close();
            statement.close();
        }
        return databases;
    }

    @Override
    public List<TableInfo> getTables(DatabaseInfo database, Connection conn) throws SQLException {
        ArrayList<TableInfo> tables = new ArrayList<>();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("SHOW FULL TABLES FROM `" + database.getDbName() + "` WHERE Table_type = 'BASE TABLE'");
        while (resultSet.next()) {
            String tableName = resultSet.getString(1);
            TableInfo table = new TableInfo();
            table.setDatasourceId(getDatasource().getId());
            table.setDatabaseId(database.getId());
            table.setDbName(database.getDbName());
            table.setTableName(tableName);
            table.setDatasourceType(DbType.MYSQL);
            tables.add(table);
        }
        resultSet.close();
        statement.close();
        return tables;
    }

    @Override
    public List<ColumnInfo> getColumns(DatabaseInfo database, TableInfo table, Connection conn) throws SQLException {
        LinkedHashMap<String, ColumnInfo> columnMap = new LinkedHashMap<>();
        ArrayList<IndexInfo> indexs = new ArrayList<>();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("SHOW CREATE TABLE `" + database.getDbName() + "`." + table.getTableName());
        resultSet.next();
        String createTableDdl = resultSet.getString(2);
        table.setDdl(createTableDdl);
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(createTableDdl, JdbcConstants.MYSQL);
        List<SQLStatement> stmtList = parser.parseStatementList();
        for (SQLStatement stmt : stmtList) {
            MySqlCreateTableStatement createTable = ((MySqlCreateTableStatement) stmt);
            String comment = ((SQLCharExpr) createTable.getComment()) != null ? ((SQLCharExpr) createTable.getComment()).getText() : "";
            table.setTableComment(comment);
            for (SQLTableElement sqlTableElement : createTable.getTableElementList()) {
                if (sqlTableElement instanceof SQLColumnDefinition) {
                    SQLColumnDefinition element = (SQLColumnDefinition) sqlTableElement;
                    ColumnInfo column = new ColumnInfo();
                    String columnName = element.getName().toString();
                    if (columnName.matches("`.*`")) {
                        columnName = columnName.substring(1, columnName.length() - 1);
                    }
                    column.setColumnName(columnName);
                    column.setDataType(element.getDataType().getName());
                    String args = "";
                    for (SQLExpr sqlExpr : element.getDataType().getArguments()) {
                        if(sqlExpr instanceof SQLIntegerExpr){
                            args += ((SQLIntegerExpr) sqlExpr).getNumber() + ",";
                        }else if(sqlExpr instanceof SQLCharExpr){
                            args += ((SQLCharExpr) sqlExpr).getText() + ",";
                        }
                    }
                    if (!StringUtils.isBlank(args)) {
                        args = args.substring(0, args.length() - 1);
                    }
                    column.setArgs(args);
                    String columnComment = element.getComment() != null ? element.getComment().toString() : "";
                    if (columnComment.matches("'.*'")) {
                        columnComment = columnComment.substring(1, columnComment.length() - 1);
                    }
                    column.setColumnComment(columnComment);
                    column.setDefVal(element.getDefaultExpr() == null || element.getDefaultExpr() instanceof SQLNullExpr ? "":element.getDefaultExpr().toString());
                    for (SQLColumnConstraint sqlColumnConstraint : element.getConstraints()) {
                        if (sqlColumnConstraint instanceof SQLNotNullConstraint) {
                            column.setIsNotNull(true);
                            break;
                        }
                    }
                    columnMap.put(columnName, column);
                } else if (sqlTableElement instanceof MySqlPrimaryKey) {
                    MySqlPrimaryKey element = (MySqlPrimaryKey) sqlTableElement;

                    for (SQLSelectOrderByItem sqlSelectOrderByItem : element.getColumns()) {
                        String primaryKeyColumn = sqlSelectOrderByItem.getExpr().toString();
                        if (primaryKeyColumn.matches("`.*`")) {
                            primaryKeyColumn = primaryKeyColumn.substring(1, primaryKeyColumn.length() - 1);
                        }
                        ColumnInfo column = columnMap.get(primaryKeyColumn);
                        column.setIsPrimkey(true);
                    }
                } else if (sqlTableElement instanceof MySqlKey) {
                    MySqlKey element = (MySqlKey) sqlTableElement;
                    IndexInfo index = new IndexInfo();
                    String indexName = element.getName().toString();
                    if (indexName.matches("`.*`")) {
                        indexName = indexName.substring(1, indexName.length() - 1);
                    }
                    index.setIndexName(indexName);
                    StringBuilder indexColumns = new StringBuilder();
                    for (SQLSelectOrderByItem sqlSelectOrderByItem : element.getColumns()) {
                        String indexColumn = sqlSelectOrderByItem.getExpr().toString();
                        if (indexColumn.matches("`.*`")) {
                            indexColumn = indexColumn.substring(1, indexColumn.length() - 1);
                        }
                        indexColumns.append(indexColumn).append(",");
                    }
                    index.setColumns(indexColumns.deleteCharAt(indexColumns.length() - 1).toString());
                    if (element instanceof MySqlUnique) {
                        index.setIsUnique(true);
                    }
                    indexs.add(index);
                }
            }
        }
        table.setIndexs(indexs);
        resultSet.close();
        statement.close();
        return new ArrayList<>(columnMap.values());
    }

    @Override
    public List<IndexInfo> getIndexs(DatabaseInfo database, TableInfo table, Connection conn) throws SQLException {
        return table.getIndexs();
    }

    @Override
    public void createNewDatabase(String dbName) throws SQLException {
        Statement statement = getConn().createStatement();
        statement.execute("create database " + dbName);
        statement.close();
    }

    @Override
    public void dropDatabase(String dbName) throws SQLException {
        Statement statement = getConn().createStatement();
        statement.execute("drop database " + dbName);
        statement.close();
    }

    @Override
    public void dropTable(String dbName, String tableName) throws SQLException {
        Statement statement = getConn().createStatement();
        statement.execute("drop table " + dbName + "." + tableName);
        statement.close();
    }

    @Override
    public List<SQLStatement> checkCreateTableDdl(String ddl) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.MYSQL);
        List<SQLStatement> sqlStatements = parser.parseStatementList();
        if(sqlStatements.size()>1){
            throw new ParserException("只允许一条建表语句");
        }
        if(!(sqlStatements.get(0) instanceof SQLCreateTableStatement)){
            throw new ParserException("第一条sql必须是建表语句");
        }
        return sqlStatements;
    }

    @Override
    public void createTableByDdl(DatabaseInfo database, List<SQLStatement> sqlStatements) throws SQLException {
        Connection conn = getConn();
        try {
            Statement statement = conn.createStatement();
            statement.execute("use `"+database.getDbName()+"`");
            for (SQLStatement sqlStatement : sqlStatements) {
                statement.execute(sqlStatement.toString());
            }
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public String generateDdlSql(TableInfo table) {
        MySqlCreateTableStatement statement = new MySqlCreateTableStatement();
        statement.setName(table.getTableName());
        statement.setComment(new SQLCharExpr(table.getTableComment()));
        MySqlPrimaryKey primaryKey = new MySqlPrimaryKey();
        List<SQLTableElement> tableElementList = statement.getTableElementList();
        List<ColumnInfo> columns = table.getColumns();
        for (ColumnInfo column : columns) {
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName(column.getColumnName());
            SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
            if(!StringUtils.isBlank(column.getArgs())){
                String[] args = column.getArgs().split(",");
                for (String arg : args) {
                    sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                }
            }
            element.setDataType(sqlDataType);
            if(column.getColumnComment()!=null){
                element.setComment(column.getColumnComment());
            }
            if(column.getIsNotNull()){
                element.addConstraint(new SQLNotNullConstraint());
            }
            if(!StringUtils.isBlank(column.getDefVal())){
                element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
            }
            if(column.getIsPrimkey()){
                primaryKey.addColumn(new SQLIdentifierExpr(column.getColumnName()));
            }
            tableElementList.add(element);
        }
        if(primaryKey.getColumns().size()>0){
            tableElementList.add(primaryKey);
        }
        //索引
        for (IndexInfo index : table.getIndexs()) {
            MySqlKey mySqlKey = new MySqlKey();
            if(index.getIsUnique()){
                mySqlKey=new MySqlUnique();
            }
            mySqlKey.setName(index.getIndexName());
            for (String s : index.getColumns().split(",")) {
                mySqlKey.getColumns().add(new SQLSelectOrderByItem(new SQLIdentifierExpr(s)));
            }
            tableElementList.add(mySqlKey);
        }
        return statement.toString();
    }

    @Override
    public String getTableNameByDdl(String ddl) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.MYSQL);
        SQLCreateTableStatement sqlCreateTableStatement = parser.getSQLCreateTableParser().parseCreateTable();
        String tableName = sqlCreateTableStatement.getName().getSimpleName();
        if (tableName.matches("`.*`")) {
            tableName = tableName.substring(1, tableName.length() - 1);
        }
        return tableName;
    }

    @Override
    public String getTableNameByModifyDdl(SQLStatement sqlStatement) {
        SQLAlterTableStatement sqlAlterTableStatement = (SQLAlterTableStatement) sqlStatement;
        String tableName =sqlAlterTableStatement.getTableName();
        if (tableName.matches("`.*`")) {
            tableName = tableName.substring(1, tableName.length() - 1);
        }
        return tableName;
    }

    @Override
    public String generateModifySql(TableInfo table, TableInfo oldTable) throws Exception {
        List<ColumnInfo> columns = table.getColumns();
        List<ColumnInfo> oldColumns = oldTable.getColumns();
        SQLAlterTableStatement statement = new SQLAlterTableStatement(JdbcConstants.MYSQL);
        statement.setName(new SQLIdentifierExpr(table.getTableName()));
        int i=0;
        //修改列
        for (;i<oldColumns.size();i++){
            ColumnInfo column = columns.get(i);
            if(!column.getColumnName().equals(oldColumns.get(i).getColumnName())){
                throw new Exception("修改列字段与原表不匹配");
            }
            MySqlAlterTableModifyColumn modifyColumn = new MySqlAlterTableModifyColumn();
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName(column.getColumnName());
            SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
            if(!StringUtils.isBlank(column.getArgs())){
                String[] args = column.getArgs().split(",");
                for (String arg : args) {
                    sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                }
            }
            element.setDataType(sqlDataType);
            if(column.getColumnComment()!=null){
                element.setComment(column.getColumnComment());
            }
            if(column.getIsNotNull()){
                element.addConstraint(new SQLNotNullConstraint());
            }
            if(!StringUtils.isBlank(column.getDefVal())){
                element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
            }
            modifyColumn.setNewColumnDefinition(element);
            statement.addItem(modifyColumn);
        }
        //新增列
        for(;i<columns.size();i++){
            ColumnInfo column = columns.get(i);
            SQLAlterTableAddColumn addColumn = new SQLAlterTableAddColumn();
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName(column.getColumnName());
            SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
            if(!StringUtils.isBlank(column.getArgs())){
                String[] args = column.getArgs().split(",");
                for (String arg : args) {
                    sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                }
            }
            element.setDataType(sqlDataType);
            if(column.getColumnComment()!=null){
                element.setComment(column.getColumnComment());
            }
            if(column.getIsNotNull()){
                element.addConstraint(new SQLNotNullConstraint());
            }
            if(!StringUtils.isBlank(column.getDefVal())){
                element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
            }
            addColumn.addColumn(element);
            statement.addItem(addColumn);
        }
        return statement.toString();
    }

    @Override
    public void modifyTableByDdl(DatabaseInfo database, List<SQLStatement> sqlStatements) throws SQLException {
        Connection conn = getConn();
        try {
            Statement statement = conn.createStatement();
            statement.execute("use `"+database.getDbName()+"`");
            for (SQLStatement sqlStatement : sqlStatements) {
                statement.execute(sqlStatement.toString());
            }
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<SQLStatement> checkModifyTableDdl(String ddl,String tableName) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.MYSQL);
        List<SQLStatement> sqlStatements = parser.parseStatementList();
        for (SQLStatement sqlStatement : sqlStatements) {
            if (!(sqlStatement instanceof SQLAlterTableStatement)) {
                throw new ParserException("修改语句类型错误");
            }
            if(!tableName.equals(getTableNameByModifyDdl(sqlStatement))){
                throw new ParserException("修改语句表名与原表不匹配");
            }
        }
        return sqlStatements;
    }

    @Override
    public List<String> getDataTypes() {
        String[] dataTypes={
                "BIGINT",
                "BINARY",
                "BIT",
                "BLOB",
                "CHAR",
                "DATE",
                "DATETIME",
                "DECIMAL",
                "DOUBLE",
                "ENUM",
                "FLOAT",
                "GEOMETRY",
                "GEOMETRYCOLLECTION",
                "INT",
                "JSON",
                "LINESTRING",
                "LONGBLOB",
                "MEDIUMBLOB",
                "MEDIUMINT",
                "MEDIUMTEXT",
                "MULTILINESTRING",
                "MULTIPOINT",
                "MULTIPOLYGON",
                "NUMERIC",
                "POINT",
                "POLYGON",
                "REAL",
                "SET",
                "SMALLINT",
                "TEXT",
                "TIME",
                "TIMESTAMP",
                "TINYBLOB",
                "TINYINT",
                "TINYTEXT",
                "VARBINARY",
                "VARCHAR",
                "YEAR"
        };
        return Arrays.asList(dataTypes);
    }

    @Override
    public String format(String sql) {
        return SQLUtils.format(sql, JdbcConstants.MYSQL);
    }

    @Override
    public String getDatasourceDriver() {
        return COM_MYSQL_JDBC_DRIVER;
    }
}
